ER Diagram

An Entity–Relationship (ER) Diagram is a type of diagram used in database design to visually represent the structure of a database. It shows how entities (tables) in a system are related to each other, along with their attributes and the type of relationships they share.

It is part of conceptual data modeling.
Helps database designers, developers, and business users understand how data is structured.

Why ER Diagrams are Important?

Helps in visualizing database design before implementation.
Ensures clear communication between developers, analysts, and stakeholders.
Identifies entities, attributes, and relationships to reduce redundancy.
Provides a blueprint to convert into Relational Schema (tables in SQL).

Key Components of an ER Diagram

Entity

An entity represents a real-world object (person, place, event, or thing) that has data stored about it.
In ER diagrams, entities are shown as rectangles.
Example: student, course, teacher.

Entities are of two types:

Strong Entity → Can exist independently.
Weak Entity → Depends on another entity for its existence.

Attributes

Attributes describe the properties or characteristics of an entity.
Shown as ellipses (ovals) connected to entities.
Example: A student entity may have attributes like student_id, name, email.

Types of Attributes:

Simple Attribute → Cannot be divided further (e.g., age).
Composite Attribute → Can be divided (e.g., full_name → first_name + last_name).
Derived Attribute → Calculated from other attributes (e.g., Age from DOB).
Key Attribute → A unique identifier for an entity (e.g., student_id).

Relationships

Relationships represent associations between entities.
Shown as diamonds in ER diagrams.
Example: A student enrolls in a course.

Cardinality of Relationships (important in database design):

One-to-One (1:1) → One entity relates to exactly one other. (Example: Each Student has one ID Card).
One-to-Many (1:N) → One entity relates to many others. (Example: One Teacher teaches many Courses).
Many-to-Many (M:N) → Many entities relate to many others. (Example: Students enroll in many Courses, and each Course has many Students).

Example of an ER Diagram

We need to design a database that stores information about Students, Courses, and Teachers.

Entities and Attributes:

student (student_id, name, email)
course (course_id, course_name, credits)
teacher (teacher_id, name, department)

Relationships:

A Student can enroll in many Courses, and a Course can have many Students → Many-to-Many.
A Teacher can teach many Courses, but each Course is taught by only one Teacher → One-to-Many.

Diagram Representation

[STUDENT] ----(enrolls in)----< [COURSE] >----(taught by)----[TEACHER]

STUDENT: {student_id (PK), name, email}
COURSE: {course_id (PK), course_name, credits}
TEACHER: {teacher_id (PK), name, department}

ERD Symbols and Notations

An Entity–Relationship Diagram (ERD) uses specific symbols and notations to represent entities, attributes, and relationships. Over time, different styles of notations have evolved. The three most commonly used are:

Chen Notation (original, most descriptive)
Crow’s Foot Notation (most widely used in industry)
UML Notation (Unified Modeling Language, used in software engineering)

Use ER diagram symbols:

Rectangles → Entities
Ellipses → Attributes
Diamonds → Relationships
Lines → Connections
Double lines → Total participation
Double rectangles → Weak entities

   student (Student_id, name, dob, email)
        |M:N
        |
    enrolls
        |
        |M:N
   course (course_id, title, credits)
        |
        |1:N
    teaches
        |
 instructor (instructor_id, name, salary)
        |
        |1:N
   department (dept_id, dept_name)

Chen’s Notation (1976)

This is the original notation introduced by Peter Chen. It is very visual and descriptive, but sometimes less compact.

Symbols in Chen’s Notation

Entity → Rectangle
Attribute → Ellipse (Oval)
- Key Attribute: underlined
- Derived Attribute: dashed oval
Relationship → Diamond
Connecting Lines → link entities, attributes, and relationships

Example

Scenario → Students enroll in Courses

 [STUDENT] ------(enrolls)------ [COURSE]
      |                               |
  (student_id)                    (course_id)
  (name)                          (course_name)

Entities: Student, Course (rectangles)
Attributes: student_id, name, course_id, course_name (ovals)
Relationship: enrolls (diamond)

Best for teaching and conceptual modeling, but diagrams can get large.

Crow’s Foot Notation

This is the most popular in database design (especially in business and IT). It focuses on entities, attributes, and cardinalities (1:1, 1:N, M:N).

Symbols in Crow’s Foot Notation

Entity → Rectangle (with attributes inside)
Primary Key → Underlined / at top
Relationships → Lines with symbols:
- One (|)
- Many (crow’s foot symbol with three lines)
- Optional (circle)
Attributes: Usually shown inside entities (not separate ovals).

Example

Scenario → Students enroll in Courses

[STUDENT] ────< enrolls >──── [COURSE]

STUDENT
---------
student_id (PK)
name
email

COURSE
---------
course_id (PK)
course_name
credits

STUDENT to COURSE → Many-to-Many (M:N) relationship
Represented by a crow’s foot near both ends
Often requires a junction table (e.g., ENROLLMENT).

Best for practical database design, easy to map directly to relational schema.

UML Notation (Unified Modeling Language)

UML is a general modeling language for software design, not just databases. However, it can be used to design ERDs too.

Symbols in UML Notation

Entity (Class) → Rectangle divided into 3 sections
1. Entity Name
2. Attributes
3. Methods (optional in ERD, often left blank in DB design)
Relationships → Lines with multiplicity notations:
- 1 → One
- 0..1 → Zero or One
- * → Many

Example:

Scenario → Students enroll in Courses

 -------------------------
 |       STUDENT         |
 -------------------------
 | student_id (PK)       |
 | name                  |
 | email                 |
 -------------------------

 -------------------------
 |       COURSE          |
 -------------------------
 | course_id (PK)        |
 | course_name           |
 | credits               |
 -------------------------

STUDENT  "0..*" --------- "1..*"  COURSE

Multiplicity (0..*, 1..*) describes cardinality.
UML looks like class diagrams in software engineering.

Best when integrating database design with object-oriented systems.

Comparison of Notations

Feature	Chen Notation	Crow’s Foot Notation	UML Notation
Focus	Conceptual modeling	Practical database design	Software + database design
Entities	Rectangles	Rectangles with attributes	Classes (rectangles)
Attributes	Ovals	Inside entity box	Inside class box
Relationships	Diamonds	Lines with crow’s foot	Lines with multiplicity
Cardinality shown	Verbally near diamond	Graphically (crow’s foot)	Numbers (, 0..1, 1..)
Usage	Academia, theory	Industry, real databases	Software engineering

Chen Notation → Best for teaching and high-level conceptual diagrams.
Crow’s Foot Notation → Best for real-world database design (most popular).
UML Notation → Best for integrating database design with software development.

Entities

In ER modeling, an Entity represents a real-world object or concept that has data stored about it in the database.

Examples: Student, Teacher, Car, Bank Account.
Entities are shown as rectangles in ER diagrams.

Each entity is described by its attributes (like ID, name, age, etc.), and one or more of these attributes act as a primary key to uniquely identify instances of that entity.

How to Identify?

Look for nouns in the requirement description.

Example:

Requirement: “Students enroll in courses, and faculty members teach courses.”

Entities: Student, Course, Faculty

Types of Entities

Strong Entity

Exists independently of other entities.
Has a primary key (unique identifier).
Represented by a single rectangle in ERD.

Example:

In a university database, the Student entity is a strong entity because:

Each student can be uniquely identified by student_id.
It exists on its own without needing another entity.

[STUDENT]
-----------
student_id (PK)
name
email

Even if no other entity exists, Student can still exist in the system.

Weak Entity

Cannot exist independently.
Depends on a Strong Entity for its existence.
Does not have a complete primary key of its own; instead, it has a partial key (called a discriminator) that combines with the strong entity’s key to create a unique identifier.
Represented by a double rectangle in ERD.
The relationship connecting it to the strong entity is called an Identifying Relationship, drawn with a double diamond.

Example

In a banking system, consider Bank Account (strong entity) and Dependent (weak entity).

Each Dependent (like spouse or child of account holder) cannot exist without being linked to an account.
dependent might have an attribute like dependent_name (partial key).
Full identification requires combining account_id (from strong entity) + dependent_name.

[ACCOUNT] ──<<Identifies>>── [DEPENDENT]

ACCOUNT (Strong Entity)
----------------------
account_id (PK)
account_type
balance

DEPENDENT (weak entity)
----------------------
dependent_name (Partial Key)
relationship

account is a strong entity (exists independently).
dependent is a weak entity (cannot exist unless tied to an account).

Key Differences Between Strong and Weak Entities

Feature	Strong Entity	Weak Entity
Existence	Independent	Dependent on a strong entity
Primary Key	Has its own primary key	Does not have a full primary key
Identification	Self-identified	Identified using strong entity’s key + partial key
ERD Symbol	Single rectangle	Double rectangle
Relationship	Normal relationship (single diamond)	Identifying relationship (double diamond)
Example	Student, Course, Teacher	Dependent, Order Item, Invoice Line

Attributes

An Attribute is a property or characteristic that describes an entity (or sometimes a relationship) in an ER model.

Example: A student entity may have attributes like student_id, name, and email.
In ER diagrams, attributes are usually represented as ellipses (ovals) connected to entities or relationships.

How to Identify?

Look for descriptive information related to an entity.

Example:

student → student_id (PK), name, phone, dob
course → course_id (PK), course_name, credit_hours
faculty → faculty_id (PK), name, department

Types of Attributes

Simple Attribute

Cannot be divided further into smaller parts.
They are atomic (indivisible).
Represented as a single oval.

Example

age of a student → atomic, cannot be further broken.
gender, salary, roll_no.

[STUDENT]
   |
 (Age)

Here, age is a simple attribute of student.

Composite Attribute

Can be divided into smaller sub-parts, each representing a more detailed attribute.
Useful for representing structured data.
Represented as an oval connected to smaller ovals.

Example

full_name can be divided into first_name and last_name.
address can be divided into street, city, state, zip_code.

          (full_name)
          /        \
 (first_name)   (last_name)

 [STUDENT]

Here, full_name is a composite attribute.

Multivalued Attribute

Can hold multiple values for a single entity instance.
Represented by a double oval.

Example

A student may have multiple phone numbers.
An employee may have multiple skills.

[STUDENT]
   |
((phone_number))

Here, one student can have multiple phone numbers, so phone_number is a multivalued attribute.

Derived Attribute

Value is derived (calculated) from other attributes.
Represented by a dashed oval.

Example

age can be derived from dob.
total_price can be derived as quantity × unit_price.

[STUDENT]
   |
 (dob) -----> (age) [Dashed oval]

Here, age is not stored directly; it is calculated from dob.

Summary Table of Attribute Types

Attribute Type	Definition	Symbol in ERD	Example
Simple	Cannot be subdivided	Oval	Age, Gender, Salary
Composite	Can be subdivided into sub-parts	Oval → sub-ovals	Full_Name (First, Last)
Multivalued	Can store multiple values	Double oval	Phone_Number, Skills
Derived	Computed from other attributes	Dashed oval	Age (from DOB), Total_Price

Relationships

A relationship in an ER model describes the association between two or more entities.

Shown as a diamond shape (Chen notation) or simply a line (Crow’s Foot notation).
Each relationship has a cardinality (the number of entity instances that can be associated).

How to Identify?

Look for verbs in the requirement description.

Example:

Requirement: “Students enroll in courses, and faculty members teach courses.”

Relationships:
- Student enrolls in Course (M:N)
- Faculty teaches Course (1:N)

Types of Relationships (by Cardinality)

One-to-One (1:1) Relationship

One entity is associated with exactly one instance of another entity.
Each side has only one matching record.

Example:

In a university, each Student has one student_id_card, and each student_id_card belongs to exactly one student.

ER Representation (text-based):

[STUDENT] 1 ──── 1 [STUDENT_ID_CARD]

A student can have only one ID card.
An ID card belongs to only one student.

Rare in practice, but used when data must be stored separately for efficiency or security reasons.

One-to-Many (1:N) Relationship

One entity is associated with many instances of another entity.
But the reverse is not true (those many belong to only one).

Example:

A Teacher teaches many Courses.
But each Course is taught by only one Teacher.

ER Representation (text-based):

[TEACHER] 1 ────< [COURSE]

One teacher → many courses.
Each course → exactly one teacher.

This is the most common type in databases.

Many-to-Many (M:N) Relationship

One entity can be associated with many instances of another, and vice versa.

Example:

A Student can enroll in many Courses.
Each Course can have many Students.

ER Representation (text-based):

[STUDENT] >───< [COURSE]

A student can enroll in multiple courses.
A course can have multiple students.

In relational databases:

M:N relationships cannot be implemented directly.
They are resolved by creating a junction (bridge) table.

Example Junction Table: ENROLLMENT

ENROLLMENT
------------------
student_id (FK)
course_id (FK)
enrollment_date

This breaks the M:N into two 1:N relationships:

student → enrollment
course → enrollment

Comparison of Relationship Types

Relationship Type	Meaning	Example	ERD Representation
1:1 (One-to-One)	One entity ↔ One entity	Student ↔ Student_ID_Card	`[STUDENT] 1───1 [STUDENT_ID_CARD]`
1:N (One-to-Many)	One entity ↔ Many entities	Teacher ↔ Courses	`[TEACHER] 1───< [COURSE]`
M:N (Many-to-Many)	Many entities ↔ Many entities	Students ↔ Courses	`[STUDENT] >───< [COURSE]`

Keys

A Key is an attribute (or set of attributes) that helps in uniquely identifying an entity in a database.

Keys ensure that there are no duplicate records and establish relationships between tables.

Types of Keys

Primary Key (PK)

A unique identifier for each entity (record).
No two rows can have the same primary key value.
Cannot be NULL.
In ER diagrams, it is often underlined.

Example:

In a STUDENT entity:

student_id can be the primary key because it uniquely identifies each student.

STUDENT
-------------------
student_id (PK)
name
email
phone

Even if two students have the same name, their student_id will be unique.

Composite Key

A primary key made up of two or more attributes.
Used when a single attribute is not enough to uniquely identify a record.

Candidate Key

All possible attributes (or combinations) that can uniquely identify an entity.
From the candidate keys, one is chosen as the primary key.
Others remain as alternative unique identifiers.

Example:

In the STUDENT entity:

student_ID (unique)
email (also unique)

So: {student_id, email} are candidate keys.

If we choose student_id as the primary key, email remains an alternate key.

Foreign Key (FK)

An attribute that creates a relationship between two entities.
It is a primary key in one table and appears as an attribute in another table.
Used to enforce referential integrity (you cannot insert a value in FK unless it exists in the referenced PK).

Example:

Consider two entities:

STUDENT
------------------
student_id (PK)
name
email

ENROLLMENT
------------------
enrollment_id (PK)
student_id (FK)
course_id (FK)

In ENROLLMENT, student_id is a foreign key referencing STUDENT(student_id).
This links each enrollment record to a student.

Super Key

A set of one or more attributes that can uniquely identify a record.
Primary key is always a super key, but not all super keys are primary keys.
Super key may contain extra attributes that are not necessary for uniqueness.

Example:

In STUDENT:

{student_id} → uniquely identifies a student.
{student_id, Name} → also uniquely identifies a student, but contains unnecessary attribute Name.

So:

{student_id}, {Email}, {student_id, Name} are super keys.
Only the minimal one (student_id) is chosen as the primary key.

Surrogate Key

An artificially created key (usually auto-incremented number or UUID).
Has no business meaning, used only for uniqueness.
Helps avoid using long/natural keys (like National_ID or Email) as primary keys.

Example

enrollment_id (PK, Auto)	student_id (FK)	course_id (FK)	Grade
1	S101	C201	A
2	S101	C202	B

enrollment_id is a surrogate key → generated automatically (1, 2, 3...).
student_id + course_id are still important, but surrogate key makes referencing easier.

Comparison Table of Keys

Key Type	Definition	Example
Primary Key	Chosen unique identifier for records; cannot be NULL	`student_id` in STUDENT
Composite Key	Uniqueness based on combination of attributes.	(student_id + course_id) in Enrollment
Candidate Key	All possible unique identifiers	`{student_id, Email}`
Foreign Key	Attribute linking two entities	`student_id` in ENROLLMENT referencing STUDENT
Super Key	Any set of attributes that uniquely identifies records (may include extra fields)	`{student_id}`, `{student_id, Name}`
Surrogate Key	Artificial key with no business meaning (auto-generated).	enrollment_id (1, 2, 3...)

Key Components of an ER Diagram​

Entity​

Attributes​

Relationships​

Example of an ER Diagram​

Diagram Representation​

ERD Symbols and Notations​

Chen’s Notation (1976)​

Symbols in Chen’s Notation​

Example​

Crow’s Foot Notation​

Symbols in Crow’s Foot Notation​

Example​

UML Notation (Unified Modeling Language)​

Symbols in UML Notation​

Example:​

Comparison of Notations​

Entities​

Types of Entities​

Strong Entity​

Weak Entity​

Key Differences Between Strong and Weak Entities​

Attributes​

Types of Attributes​

Simple Attribute​

Example​

Composite Attribute​

Example​

Multivalued Attribute​

Example​

Derived Attribute​

Example​

Summary Table of Attribute Types​

Relationships​

Types of Relationships (by Cardinality)​

One-to-One (1:1) Relationship​

One-to-Many (1:N) Relationship​

Many-to-Many (M:N) Relationship​

Comparison of Relationship Types​

Keys​

Types of Keys​

Primary Key (PK)​

Composite Key​

Candidate Key​

Foreign Key (FK)​

Super Key​

Surrogate Key​

Comparison Table of Keys​

Key Components of an ER Diagram

Entity

Attributes

Relationships

Example of an ER Diagram

Diagram Representation

ERD Symbols and Notations

Chen’s Notation (1976)

Symbols in Chen’s Notation

Example

Crow’s Foot Notation

Symbols in Crow’s Foot Notation

Example

UML Notation (Unified Modeling Language)

Symbols in UML Notation

Example:

Comparison of Notations

Entities

Types of Entities

Strong Entity

Weak Entity

Key Differences Between Strong and Weak Entities

Attributes

Types of Attributes

Simple Attribute

Example

Composite Attribute

Example

Multivalued Attribute

Example

Derived Attribute

Example

Summary Table of Attribute Types

Relationships

Types of Relationships (by Cardinality)

One-to-One (1:1) Relationship

One-to-Many (1:N) Relationship

Many-to-Many (M:N) Relationship

Comparison of Relationship Types

Keys

Types of Keys

Primary Key (PK)

Composite Key

Candidate Key

Foreign Key (FK)

Super Key

Surrogate Key

Comparison Table of Keys